"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : ustore-Consistency
Case Name   : 创建所有需要表 
Create At   : 2023/02/10
Owner       : opentestcase004
Description :
    1.建schema1
    2.建表1并插入数据
    3.建表2并插入数据
    4.建schema2
    5.建表3
    6.建存储过程并调用
    7.建表4并插入数据
    8.建表5并插入数据
    9.建表6并插入数据,为表6创建索引
    10.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功
    9.成功
    10.成功
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class CONSISTENCY(unittest.TestCase):
    def setUp(self):
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.schema1 = 's_ustore_consistency_01'
        self.schema2 = 's_ustore_consistency_02'
        self.table1 = 't_ustore_consistency_0001_01'
        self.table2 = 't_ustore_consistency_0001_02'
        self.table3 = 't_ustore_consistency_0001_03'
        self.table4 = 't_ustore_consistency_0001_04'
        self.table5 = 't_ustore_consistency_0001_05'
        self.table6 = 't_ustore_consistency_0001_06'
        self.procedure = 'p_ustore_consistency_0001'
        self.index = 'i_ustore_consistency_0001'

    def test_ustore_consistency(self):
        text1 = 'step1:建schema1 expect:成功'
        self.log.info(text1)
        create_schema = self.pri_sh.execut_db_sql(
            f'''drop schema if exists {self.schema1} cascade;
            create schema {self.schema1};''')
        self.log.info(create_schema)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, create_schema,
                      '执行失败' + text1)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, create_schema,
                      '执行失败' + text1)

        text2 = 'step2:建表1并插入数据 expect:成功'
        self.log.info(text2)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema1}.{self.table1};
            create table {self.schema1}.{self.table1}(c_id int,c_d_id int 
            not null,c_w_id int not null,c_first varchar(16) not null,
            c_middle char(2),c_last varchar(16) not null,c_street_1 
            varchar(20) not null,c_street_2 varchar(20),c_city varchar(20) 
            not null,c_state char(2) not null,c_zip char(9) not null,
            c_phone char(16) not null,c_since timestamp,c_credit char(2) 
            not null,c_credit_lim numeric(12,2),c_discount numeric(4,4),
            c_balance numeric(12,2),c_ytd_payment real not null,
            c_payment_cnt number not null,c_delivery_cnt bool not null,
            c_end date not null,c_vchar varchar(1000),c_data clob,c_text 
            blob,primary key (c_id,c_d_id,c_w_id)) with 
            (storage_type=ustore);''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text2)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text2)
        insert_res = self.pri_sh.execut_db_sql(
            f"insert into {self.schema1}.{self.table1} values"
            f"(1,1,1,'is'||'1'||'cmRDs'||'1','OE','BAR'||'1'||'Bar'||'1',"
            f"'bkili'||'1'||'fcrRGF'||'1','pmbwo'||'1'||'vhSDGJ'||'1','dyf'"
            f"||'1'||'rDa'||'1','uq',4801||'1',940||'1'||215||'1',"
            f"to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'GC',50001.0,"
            f"0.4361328,-10.0,10.0,1,true,to_char(sysdate,"
            f"'yyyy-mm-dd hh24:mi:ss'),'QVLDETANRBRBURBMfhlhfrcllgfSMNTECC34"
            f"8493214893542NPFZCSfjlufvCDSFQVLDETANRBRBURBMfhlhfrcllgfSMNTE"
            f"CC348493214893542NPFZCSfjlufvCDSFQVLDETANRBRBURBMfhlhfrcllgfS"
            f"MNTECC348493214893542NPFZCSfjlufvCDSFQVLDETANRBRBURBMfhlhfrc"
            f"llgfSMNTECC348493214893542NPFZCSfjlufvCDSFQVLDETANRBRBURBMf"
            f"hlhfrcllgfSMNTECC348493214893542NPFZCSfjlufvCDSFQVLDETANRBRB"
            f"URBMfhlhfrcllgfSMNTECC348493214893542NPFZCSfjlufvCDSF'||'1',"
            f"'QVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSCD"
            f"SFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSC"
            f"DSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYS"
            f"CDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXY"
            f"SCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXX"
            f"YSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSY"
            f"KXXYSCDSF'||'1','123435458764312345521344565672312342455456677"
            f"6763221132454566768767433242323445453565654542323');"
            f"begin "
                f"for i in 1..1000 LOOP "
                    f"insert into {self.schema1}.{self.table1} select "
                    f"c_id+i,c_d_id+i,c_w_id+i,'isi'||i,c_middle,'BAR'||i,"
                    f"c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,"
                    f"c_since,c_credit,c_credit_lim,c_discount,c_balance,"
                    f"c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_end,"
                    f"c_vchar,c_data,c_text from "
                    f"{self.schema1}.{self.table1} where c_id=1;"
                f"end loop;"
            f"end;")
        self.log.info(insert_res)
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG,
                      insert_res, '执行失败' + text2)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_res, '执行失败'
                      + text2)

        text3 = 'step2:建表2并插入数据 expect:成功'
        self.log.info(text3)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema1}.{self.table2};
            create table {self.schema1}.{self.table2}(c_clob1 clob,c_clob2 
            clob,c_blob1 blob,c_blob2 blob);''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text3)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text3)
        insert_res = self.pri_sh.execut_db_sql(
            f"insert into {self.schema1}.{self.table2} values("
            f"'QVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXY"
            f"SCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKX"
            f"XYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCS"
            f"YKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPF"
            f"ZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvN"
            f"PFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkg"
            f"vNPFZCSYKXXYSCDSF1QVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfc"
            f"mkgvNPFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvd"
            f"fcmkgvNPFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmg"
            f"fvdfcmkgvNPFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjg"
            f"fvmgfvdfcmkgvNPFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMj"
            f"jjjgfvmgfvdfcmkgvNPFZCSYKXXYSCDSFQVLDETANRBRBUflnfufscHOQNGG"
            f"SMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSCDSF1','SYKXXYSCDSFQVLDETAN"
            f"RBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSCDSFQVLD"
            f"ETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSCDSF"
            f"QVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXYSC"
            f"DSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYKXXY"
            f"SCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZCSYK"
            f"XXYSCDSFQVLDETANRBRBUflnfufscHOQNGGSMjjjjgfvmgfvdfcmkgvNPFZC',"
            f"'12343545876431234552134456567231234245545667767632211324545"
            f"667687674332423234454535656545423231234354587643123455213445"
            f"656723123424554566776763221132454566768767433242323445453565"
            f"654542323','324545667687674332423234454535656545423231234354"
            f"5876431234552134456567231234245545667767632211');")
        self.log.info(insert_res)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_res,
                      '执行失败' + text3)

        text4 = 'step4:建schema2 expect:成功'
        self.log.info(text4)
        create_schema = self.pri_sh.execut_db_sql(
            f'''drop schema if exists {self.schema2} cascade;
            create schema {self.schema2};''')
        self.log.info(create_schema)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, create_schema,
                      '执行失败' + text4)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, create_schema,
                      '执行失败' + text4)

        text5 = 'step5:建表3 expect:成功'
        self.log.info(text5)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema2}.{self.table3};
            create table {self.schema2}.{self.table3}(c_int int) with 
            (storage_type=ustore);''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text5)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text5)

        text6 = 'step6:建存储过程并调用 expect:成功'
        self.log.info(text6)
        create_procedure = self.pri_sh.execut_db_sql(
            f'''create or replace procedure {self.schema2}.{self.procedure}() 
            is
             b int := 0;
                begin
                    for i in 1..1000 LOOP
                        insert into {self.schema2}.{self.table3} values(i);
                    end loop;
                end;''')
        self.log.info(create_procedure)
        call_procedure = self.pri_sh.execut_db_sql(
            f'''call {self.schema2}.{self.procedure}();
            select count(*) from {self.schema2}.{self.table3};''')
        self.log.info(call_procedure)
        self.assertIn('1000', call_procedure, '执行失败' + text6)

        text7 = 'step7:建表4并插入数据 expect:成功'
        self.log.info(text7)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema2}.{self.table4};
            create table {self.schema2}.{self.table4}(c_id int,c_d_id int 
            not null,c_w_id int not null,c_first varchar(16) not null,
            c_middle char(2),c_last varchar(16) not null,
            c_street_1 varchar(20) not null,c_street_2 varchar(20),
            c_city varchar(20) not null,c_state char(2) not null,
            c_zip char(9) not null,c_phone char(16) not null,
            c_since timestamp,c_credit char(2) not null,c_credit_lim number,
            c_discount number,c_balance number,c_ytd_payment number not null,
            c_payment_cnt int not null,c_delivery_cnt int not null,
            c_data clob not null) with (storage_type=ustore);

            insert into {self.schema2}.{self.table4} values (1,1,1,'aaaaa',
            'ab','dafkldafkadbfeod','dafkldafkadbfeodefgh',
            'dafkldafkadbfeodefgh','dafkldafkadbfeodefgh','xy','acgfdagfs',
            'dafkldafkadbfeod',null,'ad',50000.0,0.4491328,-10.0,10.0,1,0,
            lpad('qraogfdamjkfdlhgmdjf',4000,'qraogfdamjkfdlhgmdjf'));
            insert into {self.schema2}.{self.table4} select c_id+1,c_d_id+1,
            c_w_id+1,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+2,c_d_id+2,
            c_w_id+2,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+4,c_d_id+4,
            c_w_id+4,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+8,c_d_id+8,
            c_w_id+8,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+16,c_d_id+16,
            c_w_id+16,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+32,c_d_id+32,
            c_w_id+32,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+64,c_d_id+64,
            c_w_id+64,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,
            c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,
            c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data from 
            {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+128,
            c_d_id+128,c_w_id+128,c_first,c_middle,c_last,c_street_1,
            c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,
            c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,
            c_delivery_cnt,c_data from {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+256,
            c_d_id+256,c_w_id+256,c_first,c_middle,c_last,c_street_1,
            c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,
            c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,
            c_delivery_cnt,c_data from {self.schema2}.{self.table4};
            insert into {self.schema2}.{self.table4} select c_id+512,
            c_d_id+512,c_w_id+512,c_first,c_middle,c_last,c_street_1,
            c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,
            c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,
            c_delivery_cnt,c_data from {self.schema2}.{self.table4};
            delete from {self.schema2}.{self.table4} where c_id>1000;''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text7)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text7)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, create_table,
                      '执行失败' + text7)
        self.assertEqual(create_table.count(self.constant.INSERT_SUCCESS_MSG), 
                         11, '执行失败' + text7)

        text8 = 'step8:建表5并插入数据 expect:成功'
        self.log.info(text8)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema2}.{self.table5};
            create table {self.schema2}.{self.table5}(c_id int,c_d_id int not 
            null,c_w_id int not null,c_first varchar(200) not null,c_middle 
            char(2),c_last varchar(16) not null,c_street_1 varchar(20) 
            not null,c_street_2 varchar(20),c_city varchar(20) not null,
            c_state char(2) not null,c_zip char(9) not null,c_phone char(16) 
            not null,c_since timestamp,c_credit char(2) not null,c_credit_lim 
            number,c_discount number,c_balance number,c_ytd_payment number not 
            null,c_payment_cnt int not null,c_delivery_cnt int not null,c_data 
            clob not null) with (storage_type=ustore);

            insert into {self.schema2}.{self.table5} select c_int,c_int,c_int,
            'aaaaa','ab','dafkldafkadbfeod','dafkldafkadbfeodefgh',
            'dafkldafkadbfeodefgh','dafkldafkadbfeodefgh','xy','acgfdagfs',
            'dafkldafkadbfeod',null,'ad',50000.0,0.4491328,-10.0,10.0,1,0,
            lpad('qraogfdamjkfdlhgmdjf',4000,'qraogfdamjkfdlhgmdjf') from 
            {self.schema2}.{self.table3};''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text8)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text8)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, create_table,
                      '执行失败' + text8)

        text9 = 'step9:建表6并插入数据,为表6创建索引 expect:成功'
        self.log.info(text9)
        create_table = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.schema2}.{self.table6};
            create table {self.schema2}.{self.table6}(c_id int,c_d_id int not 
            null,c_w_id int not null,c_first varchar(200) not null,
            c_middle char(2),c_last varchar(16) not null,c_street_1 
            varchar(20) not null,c_street_2 varchar(20),c_city varchar(20) 
            not null,c_state char(2) not null,c_zip char(9) not null,c_phone 
            char(16) not null,c_since timestamp,c_credit char(2) not null,
            c_credit_lim number,c_discount number,c_balance number,
            c_ytd_payment number not null,c_payment_cnt int not null,
            c_delivery_cnt int not null,c_data clob not null) with 
            (storage_type=ustore);
            drop index if exists {self.schema2}.{self.index};
            create unique index {self.schema2}.{self.index} on 
            {self.schema2}.{self.table6} (c_id,c_d_id,c_w_id);
            insert into {self.schema2}.{self.table6} select c_int,c_int,c_int,
            'aaaaa','ab','dafkldafkadbfeod','dafkldafkadbfeodefgh',
            'dafkldafkadbfeodefgh','dafkldafkadbfeodefgh','xy','acgfdagfs',
            'dafkldafkadbfeod',null,'ad',50000.0,0.4491328,-10.0,10.0,1,0,
            lpad('qraogfdamjkfdlhgmdjf',4000,'qraogfdamjkfdlhgmdjf') from 
            {self.schema2}.{self.table3};''')
        self.log.info(create_table)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table,
                      '执行失败' + text9)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table,
                      '执行失败' + text9)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, create_table,
                      '执行失败' + text9)
        self.assertIn(self.constant.DROP_INDEX_SUCCESS_MSG, create_table,
                      '执行失败' + text9)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, create_table,
                      '执行失败' + text9)

    def tearDown(self):
        text10 = '--------无需清理环境--------'
        self.log.info(text10)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
